import sqlite, { closeConnect, excute } from '../utils/sqlite';

require("../utils/string");
require("../utils/array");
var moment = require('moment');

export default class Model {

      constructor() {
            this.mainTable = null;
            this.pk = null;
            this.serverPk = null;
            this.orderBy = '`sort_order` ASC';
            this.versionName = null;
            this.lastUpdatedName = null;
            this.dateAddedName = null;
      }

      tableName(name) {
            return name;
      }

      escape(str) {
            return str.addslashes();
      }

      getNowtime(format = 'YYYY-MM-DD hh:mm:ss') {
            return moment().format(format);
      }

      fetchOne(sql, params = []) {
            return new Promise((resolve,reject) => {
                  sqlite.executeSql(sql, params, (result) => {
                        let rows = result.rows.raw();
                        if (typeof(rows) == 'object') {
                              if (rows[0]) {
                                    resolve(rows[0]);
                              } else {
                                    resolve(null);
                              }
                        }
                  }, (err)=> {
                        reject(err);
                  });
            });
      }

      fetchRows(sql, params = [], start = 0, limit = 0) {
            start = parseInt(start);
            limit = parseInt(limit);
            if (limit > 0) {
                  sql = sprintf("%s LIMIT %d, %d", sql, start, limit);
            }
            //console.log("sql >>>> ", sql);
            return new Promise((resolve,reject) => {
                  sqlite.executeSql(sql, params, (result) => {
                        resolve(result.rows.raw());
                  }, (err)=> {
                        reject(err);
                  });
            });
      }

      numRows(table, params = {}) {
            let data = [];
            let sql = sprintf("SELECT count(0) AS num FROM `%s`",
			this.tableName(table)
            );
            if (typeof(params) == 'object') {
                  let keys = Object.keys(params);
                  if (keys.length > 0) {
                        let wheres = [];
                        keys.forEach(key => {
                              let value = params[key];
                              if (value === null) {
                                    wheres.push(key);
                              } else {
                                    wheres.push(sprintf("`%s` = ?", key));
                                    data.push(value);
                              }
                        });
                        sql = sprintf("%s WHERE %s", sql, 
                              wheres.join(" AND ")
                        );
                  }
            }
            
            //console.log("sql >>> ", sql);
            return new Promise((resolve,reject) => {
                  sqlite.executeSql(sql, data, (result) => {
                        let rows = result.rows.raw();
                        resolve(rows[0]['num']);
                  }, (err)=> {
                        reject(err);
                  });
            });
      }

      insert(table, values = {}) {
            let columns = [];
            let placeholders = [];
            let data = [];

            Object.keys(values).forEach(function(key) {
                  let value = values[key];
                  if (value !== null) {
                        columns.push(sprintf("`%s`", key));
                        placeholders.push("?");
                        if (typeof(value) == 'string' && (value.upper() == 'NULL' || value.upper() == 'NOW()')) {
                              if (value.upper() == 'NULL') {
                                    data.push(null);
                              } else {
                                    data.push(this.getNowtime());
                              }
                        } else {
                              data.push(value);
                        }
                  }
            });

            let sql = sprintf("INSERT INTO `%s` (%s) VALUES (%s)", 
                  this.tableName(table),
                  columns.join(", "),
                  placeholders.join(", ")
            );

            return new Promise((resolve,reject) => {
                  sqlite.executeSql(sql, data, (result) => {
                        resolve(result.insertId);
                  }, (err)=> {
                        reject(err);
                  });
            });
      }

      update(table, values = {}, params = {}) {
            let columns = [];
            let data = [];

            Object.keys(values).forEach(function(key) {
                  let value = values[key];
                  if (value !== null) {
                        columns.push(sprintf("`%s` = ?", key));
                        if (typeof(value) == 'string' && (value.upper() == 'NULL' || value.upper() == 'NOW()')) {
                              if (value.upper() == 'NULL') {
                                    data.push(null);
                              } else {
                                    data.push(this.getNowtime());
                              }
                        } else {
                              data.push(value);
                        }
                  }
            });

            let sql = sprintf("UPDATE `%s` SET %s", 
                  this.tableName(table),
                  columns.join(", ")
            );

            if (typeof(params) == 'object') {
                  let keys = Object.keys(params);
                  if (keys.length > 0) {
                        let wheres = [];
                        keys.forEach(key => {
                              let value = params[key];
                              if (value === null) {
                                    wheres.push(key);
                              } else {
                                    wheres.push(sprintf("`%s` = ?", key));
                                    data.push(value);
                              }
                        });
                        sql = sprintf("%s WHERE %s", sql, 
                              wheres.join(" AND ")
                        );
                  }
            }

            console.log("update sql >>> ", sql);

            return new Promise((resolve,reject) => {
                  sqlite.executeSql(sql, data, (result) => {
                        resolve(result.rowsAffected);
                  }, (err)=> {
                        reject(err);
                  });
            });
      }

      delete(table, params = {}) {
            let data = [];
            let sql = sprintf("DELETE FROM `%s`",
			this.tableName(table)
            );
            if (typeof(params) == 'object') {
                  let keys = Object.keys(params);
                  if (keys.length > 0) {
                        let wheres = [];
                        keys.forEach(key => {
                              let value = params[key];
                              if (value === null) {
                                    wheres.push(key);
                              } else {
                                    wheres.push(sprintf("`%s` = ?", key));
                                    data.push(value);
                              }
                        });
                        sql = sprintf("%s WHERE %s", sql, 
                              wheres.join(" AND ")
                        );
                  }
            }
            
            //console.log("sql >>> ", sql);
            return new Promise((resolve,reject) => {
                  sqlite.executeSql(sql, data, (result) => {
                        resolve(result.rowsAffected);
                  }, (err)=> {
                        reject(err);
                  });
            });
      }

      /**
       * 数据CRUD操作函数
       */
      async query(start = 0, limit = 0) {
            return await this.fetchRows(
                  sprintf("SELECT * FROM `%s` ORDER BY %s",  this.mainTable, this.orderBy), 
                  [], start, limit
            );
      }

      async count(params = {}) {
            return await this.numRows(this.mainTable, params);
      }

      async get(pk) {
            return await this.fetchOne(sprintf("SELECT * FROM `%s` WHERE `%s` = ? LIMIT 0,1", 
                  this.mainTable, this.pk), [pk]
            );
      }

      async save(data, pk = 0) {
            pk = parseInt(pk);
            if (pk > 0) {
                  let params = {};
                  params[this.pk] = pk;
                  if (this.lastUpdatedName !== null) {
                        data[this.lastUpdatedName] = this.getNowtime();
                  }
                  await this.update(this.mainTable, data, params);
            } else {
                  if (this.dateAddedName !== null) {
                        data[this.dateAddedName] = this.getNowtime();
                  }
                  pk = await this.insert(this.mainTable, data);
            }
            return pk;
      }

      async remove(pk = 0) {
            let params = {};
            params[this.pk] = pk;
            return await this.delete(this.mainTable, params);
      }

      /**
       * 获取当前数据表中所有的远程ID列表
       */
      async getServerPks() {
            let ids = [];
            let rows = await this.fetchRows(sprintf("SELECT `%s` as server_pk FROM `%s`", this.serverPk, this.mainTable), []);
            for (row of rows) {
                  ids.push(row.server_pk);
            }
            return ids;
      }

      /**
       * 通过serverId获取数据记录
       */
      async getByServerPk(serverPk) {
            return await this.fetchOne(sprintf("SELECT * FROM `%s` WHERE `%s` = ? LIMIT 0, 1", 
                  this.mainTable, this.serverPk), [serverPk]
            );
      }

      /**
       * 通过serverId获取数据的主键ID
       */
      async getIdByServerPk(serverPk) {
            let row = await this.fetchOne(sprintf("SELECT `%s` as pk FROM `%s` WHERE `%s` = ? LIMIT 0, 1", 
                  this.pk, this.mainTable, this.serverPk), [serverPk]
            );
            if (row && row.pk) {
                  return row.pk;
            }
            return 0;
      }

      /**
       * 通过serverPk获取当前数据的version值
       */
      async getVersionByServerPk(serverPk) {
            let row = await this.fetchOne(sprintf("SELECT `%s` as version FROM `%s` WHERE `%s` = ? LIMIT 0, 1", 
                  this.versionName, this.mainTable, this.serverPk),
                  [serverPk]
            );
            if (row && row.version) {
                  return row.version;
            }
            return 0;
      }
      
      /**
       * 通过serverPk保存数据
       */
      async saveByServerPk(data, serverPk = 0) {
            let pk = await this.getIdByServerPk(serverPk);
            let id = 0;
            if (pk > 0) {
                  let params = {};
                  params[this.pk] = pk;
                  if (this.versionName !== null) {
                        params[sprintf("`%s` <= %d", this.versionName, data.version)] = null;
                  }
                  let rowsAffected = await this.update(this.mainTable, data, params);
                  if (rowsAffected > 0) {
                        id = pk;
                  }
            } else {
                  data[this.serverPk] = serverPk;
                  id = await this.insert(this.mainTable, data);
            }
            return id;
      }
      
      /**
       * 通过serverPk删除数据
       */
      async removeByServerPk(serverPk = 0) {
            let params = {};
            params[this.serverPk] = serverPk;
            return await this.delete(this.mainTable, params);
      }
}